﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WaveMaster.Holidays.Club.SQL
{
    public class PassportSQL
    {
        public static string GetAllPassport()
        {
            string sql = "select * from passport LIMIT 100";
            return sql;
        }

        public static string GetMaxPassportID()
        {
            string sql = "select max(m_passportID) from passport ";
            return sql;
        }

        public static string GetPassportByID()
        {
            string sql = "select * from passport where m_passportID = ?m_passportID ";
            return sql;
        }
        public static string GetPassportByPassportNo()
        {
            string sql = "select * from passport WHERE m_passportNo = ?m_passportNo";
            return sql;
        }
        public static string SearchPassportByCodeName(string p)
        {
            string sql = "select * from passport WHERE concat(m_passportNo,m_name) like '%" + p + "%'";
            return sql;
        }
        public static string SearchPassportByCode(string p)
        {
            string sql = "select * from passport WHERE m_passportNo = ?m_passportNo";
            return sql;
        }
        public static string DeletePassportByID()
        {
            return @"DELETE FROM passport
      WHERE m_passportID = ?m_passportID";
        }
        public static string InsertPassport()
        {
            string sql = @"INSERT INTO passport
           (m_passportNo
           ,m_issueDate
           ,m_expireDate
           ,m_country
           ,m_officeIssue
           ,m_name
           ,m_gender
           ,m_birthPlace
           ,m_birthDate
           ,m_nationality
           ,m_placeOfResidence            
            ,m_occupation
            ,m_maritalStatus    
            ,m_address
            ,m_city
            ,m_zipcode
            ,m_remark
            ,m_cretnDate    
            ,m_cretnBy
            ,m_modfctnDate
            ,m_modfctnBy
            ,m_idnumber
            )
     VALUES
           (?m_passportNo
           ,?m_issueDate
           ,?m_expireDate
           ,?m_country
           ,?m_officeIssue
           ,?m_name
           ,?m_gender
           ,?m_birthPlace
           ,?m_birthDate
           ,?m_nationality
           ,?m_placeOfResidence            
            ,?m_occupation
            ,?m_maritalStatus    
            ,?m_address
            ,?m_city
            ,?m_zipcode
            ,?m_remark
            ,?m_cretnDate    
            ,?m_cretnBy
            ,?m_modfctnDate
            ,?m_modfctnBy
            ,?m_idnumber)";
            return sql;
        }
        public static string UpdatePassport()
        {
            string sql = @"UPDATE passport
   SET m_passportNo=?m_passportNo
           ,m_issueDate=?m_issueDate
           ,m_expireDate=?m_expireDate
           ,m_country=?m_country
           ,m_officeIssue=?m_officeIssue
           ,m_name=?m_name
           ,m_gender=?m_gender
           ,m_birthPlace=?m_birthPlace
           ,m_birthDate=?m_birthDate
           ,m_nationality=?m_nationality
           ,m_placeOfResidence=?m_placeOfResidence           
            ,m_occupation=?m_occupation
            ,m_maritalStatus=?m_maritalStatus    
            ,m_address=?m_address
            ,m_city=?m_city
            ,m_zipcode=?m_zipcode
            ,m_remark=?m_remark
            ,m_modfctnDate=?m_modfctnDate
            ,m_modfctnBy=?m_modfctnBy
            ,m_idnumber=?m_idnumber
 WHERE m_passportID = ?m_passportID";
            return sql;
        }

        public static string GetAllCountry()
        {
            string sql = "select * from country order by m_countryName";
            return sql;
        }
        public static string InsertCountry()
        {
            string sql = @"INSERT INTO country
            (m_countryCode
           ,m_countryName)
            VALUES
           (?m_countryCode
           ,?m_countryName
            )";
            return sql;
        }
    }
}
